﻿Imports MySql.Data.MySqlClient

Public Class checkRoomAvailabilityClass
    Dim connString As String = "Server=localhost; User Id=root; Password=; Database=leximoore"
    Dim dr As MySqlDataReader

    Public Function checkRooms(ByVal roomnum)
        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "SELECT room_no FROM roominventory WHERE room_no = " + CStr(roomnum)
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                End With
                Try
                    SQLConnection.Open()
                    Dim sa As New MySqlDataAdapter
                    Dim table As New DataTable
                    With sa
                        .SelectCommand = sqlCommand
                        .Fill(table)
                    End With
                    Dim roomNo As Integer
                    For i = 0 To table.Rows.Count - 1
                        roomNo = table.Rows(i)("room_no")
                        If roomNo <> 0 Then
                            Return "Occupied"
                        Else
                            Return "Available"
                        End If
                    Next
                    Return "Available"
                Catch ex As MySqlException
                    MsgBox(ex.ToString)
                    Console.WriteLine(ex)
                    Console.ReadLine()
                    Return "Available"
                Finally
                    SQLConnection.Close()
                End Try
            End Using
        End Using
    End Function

    Public Function checkRoomTypeAva()

        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()

                With sqlCommand
                    .CommandText = "SELECT qty FROM room"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                End With
                Try
                    SQLConnection.Open()
                    dr = sqlCommand.ExecuteReader()
                    Dim word As String = ""
                    While (dr.Read())
                        word += " " + dr.GetString(0)
                    End While
                    Return word

                Catch ex As MySqlException
                    MessageBox.Show(ex.Message)
                    Console.ReadLine()
                Finally
                    SQLConnection.Close()
                End Try
            End Using
        End Using
    End Function
End Class
